package com.example.icedcap.topeka_fake.persistance;

import android.content.ContentValues;
import android.content.Context;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;

import com.example.icedcap.topeka_fake.R;
import com.example.icedcap.topeka_fake.helper.JsonHelper;
import com.example.icedcap.topeka_fake.model.Category;
import com.example.icedcap.topeka_fake.model.JsonAttributes;
import com.example.icedcap.topeka_fake.model.Theme;
import com.example.icedcap.topeka_fake.model.quiz.AlphaPickerQuiz;
import com.example.icedcap.topeka_fake.model.quiz.FillBlankQuiz;
import com.example.icedcap.topeka_fake.model.quiz.FillTwoBlanksQuiz;
import com.example.icedcap.topeka_fake.model.quiz.FourQuarterQuiz;
import com.example.icedcap.topeka_fake.model.quiz.MultiSelectQuiz;
import com.example.icedcap.topeka_fake.model.quiz.PickerQuiz;
import com.example.icedcap.topeka_fake.model.quiz.Quiz;
import com.example.icedcap.topeka_fake.model.quiz.SelectionItemQuiz;
import com.example.icedcap.topeka_fake.model.quiz.ToggleTranslateQuiz;
import com.example.icedcap.topeka_fake.model.quiz.TrueFalseQuiz;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * Created by doushuqi on 15-7-13下午10:38.
 * Email:doushuqi0409@gmail.com
 */
public class TopekaDatabaseHelper extends SQLiteOpenHelper {
    private static final String TAG = TopekaDatabaseHelper.class.getSimpleName();
    private static final String DB_NAME = "topeka";
    private static final String DB_SUFFIX = ".db";
    private static final int DB_VERSION = 1;
    private static TopekaDatabaseHelper mTopekaDatabaseHelper;
    private final Resources mResources;
    private static List<Category> mCategories;

    private TopekaDatabaseHelper(Context context) {
        super(context, DB_NAME + DB_SUFFIX, null, DB_VERSION);
        mResources = context.getResources();
    }

    public static TopekaDatabaseHelper getInstance(Context context) {
        if (null == mTopekaDatabaseHelper) {
            mTopekaDatabaseHelper = new TopekaDatabaseHelper(context);
        }
        return mTopekaDatabaseHelper;
    }

    public static List<Category> getCategories(Context context, boolean fromDatabase) {
        if (null == mCategories || fromDatabase) {
            mCategories = loadCategories(context);
        }
        return mCategories;
    }

    public static void updateCategory(Context context, Category category) {
        if (mCategories != null && mCategories.contains(category)) {
            final int location = mCategories.indexOf(category);
            mCategories.remove(location);
            mCategories.add(category);
        }
        SQLiteDatabase writableDatabase = getWritableDatabase(context);
        ContentValues contentValues = createContentValuesFor(category);
        writableDatabase.update(CategoryTable.NAME, contentValues, CategoryTable.COLUMN_ID + "=?",
                new String[]{category.getId()});
        //update quizzes
    }

    public static void reset(Context context) {
        SQLiteDatabase writable = getWritableDatabase(context);
        writable.delete(CategoryTable.NAME, null, null);
        writable.delete(QuizTable.NAME, null, null);
        getInstance(context).preFillDatabase(writable);
    }

    public static Category getCategoryWith(Context context, String categoryId) {
        SQLiteDatabase readableDatabase = getReadableDatabase(context);
        String[] selectArgs = new String[]{categoryId};
        Cursor data = readableDatabase.query(CategoryTable.NAME, CategoryTable.PROJECTION,
                CategoryTable.COLUMN_ID + "=?", selectArgs, null, null, null);
        data.moveToFirst();
        return getCategory(data, readableDatabase);
    }

    private static ContentValues createContentValuesFor(Category category) {
        ContentValues values = new ContentValues();
        values.put(CategoryTable.COLUMN_SCORES, Arrays.toString(category.getScores()));
        values.put(CategoryTable.COLUMN_SOLVED, category.isSolved());

        return values;
    }

    private static SQLiteDatabase getReadableDatabase(Context context) {
        return getInstance(context).getReadableDatabase();
    }

    private static SQLiteDatabase getWritableDatabase(Context context) {
        return getInstance(context).getWritableDatabase();
    }

    private static Cursor getCategoryCursor(Context context) {
        SQLiteDatabase readableDatabase = getReadableDatabase(context);
        Cursor data = readableDatabase.query(CategoryTable.NAME, CategoryTable.PROJECTION, null, null, null, null, null);
        data.moveToFirst();
        return data;
    }

    private static List<Category> loadCategories(Context context) {
        Cursor data = getCategoryCursor(context);
        List<Category> tmpCategories = new ArrayList<>(data.getCount());
        final SQLiteDatabase readableDatabase = getReadableDatabase(context);
        do {
            final Category category = getCategory(data, readableDatabase);
            tmpCategories.add(category);
        } while (data.moveToNext());
        return tmpCategories;
    }

    private static Category getCategory(Cursor cursor, SQLiteDatabase database) {
        final String id = cursor.getString(cursor.getColumnIndex(CategoryTable.COLUMN_ID));
        final String name = cursor.getString(cursor.getColumnIndex(CategoryTable.COLUMN_NAME));
        final String themeName = cursor.getString(cursor.getColumnIndex(CategoryTable.COLUMN_THEME));
        final Theme theme = Theme.valueOf(themeName);
        final String isSolved = cursor.getString(cursor.getColumnIndex(CategoryTable.COLUMN_SOLVED));
        final boolean solved = getBooleanFromDatabase(isSolved);
        final int[] scores = JsonHelper.jsonArrayToIntArray(cursor.getString(cursor.getColumnIndex(CategoryTable.COLUMN_SCORES)));
        return new Category(name, id, theme, scores, getQuizzes(id, database), solved);
    }

    private static List<Quiz> getQuizzes(final String categoryId, SQLiteDatabase database) {
        final List<Quiz> quizzes = new ArrayList<>();
        final Cursor cursor = database.query(QuizTable.NAME, QuizTable.PROJECTION,
                QuizTable.FK_CATEGORY + " LIKE ?", new String[]{categoryId}, null, null, null);
        cursor.moveToFirst();
        do{
            quizzes.add(createQuizDueToType(cursor));
        }while (cursor.moveToNext());
        return quizzes;
    }

    private static Quiz createQuizDueToType(Cursor cursor) {
        final String type = cursor.getString(2);
        final String question = cursor.getString(3);
        final String answer = cursor.getString(4);
        final String options = cursor.getString(5);
        final int min = cursor.getInt(6);
        final int max = cursor.getInt(7);
        final int step = cursor.getInt(8);
        final boolean solved = getBooleanFromDatabase(cursor.getString(11));

        switch (type) {
            case JsonAttributes.QuizType.ALPHA_PICKER: {
                return new AlphaPickerQuiz(question, answer, solved);
            }
            case JsonAttributes.QuizType.FILL_BLANK: {
                return createFillBlankQuiz(cursor, question, answer, solved);
            }
            case JsonAttributes.QuizType.FILL_TWO_BLANKS: {
                return createFillTwoBlanksQuiz(question, answer, solved);
            }
            case JsonAttributes.QuizType.FOUR_QUARTER: {
                return createFourQuarterQuiz(question, answer, options, solved);
            }
            case JsonAttributes.QuizType.MULTI_SELECT: {
                return createMultiSelectQuiz(question, answer, options, solved);
            }
            case JsonAttributes.QuizType.PICKER: {
                return new PickerQuiz(question, Integer.valueOf(answer), solved, min, max, step);
            }
            case JsonAttributes.QuizType.SINGLE_SELECT:
                //fall-through intended
            case JsonAttributes.QuizType.SINGLE_SELECT_ITEM: {
                return createSelectItemQuiz(question, answer, options, solved);
            }
            case JsonAttributes.QuizType.TOGGLE_TRANSLATE: {
                return createToggleTranslateQuiz(question, answer, options, solved);
            }
            case JsonAttributes.QuizType.TRUE_FALSE: {
                return createTrueFalseQuiz(question, answer, solved);

            }
            default: {
                throw new IllegalArgumentException("Quiz type " + type + " is not supported");
            }
        }
    }

    private static Quiz createFillBlankQuiz(Cursor cursor, String question, String answer,
                                            boolean solved) {
        final String start = cursor.getString(9);
        final String end = cursor.getString(10);
        return new FillBlankQuiz(question, answer, solved, start, end);
    }

    private static Quiz createFillTwoBlanksQuiz(String question, String answer, boolean solved) {
        final String[] answerArray = JsonHelper.jsonArrayToStringArray(answer);
        return new FillTwoBlanksQuiz(question, answerArray, solved);
    }

    private static Quiz createFourQuarterQuiz(String question, String answer, String options,
                                              boolean solved) {
        final int[] answerArray = JsonHelper.jsonArrayToIntArray(answer);
        final String[] optionsArray = JsonHelper.jsonArrayToStringArray(options);
        return new FourQuarterQuiz(question, answerArray, optionsArray, solved);
    }

    private static Quiz createMultiSelectQuiz(String question, String answer, String options,
                                              boolean solved) {
        final int[] answerArray = JsonHelper.jsonArrayToIntArray(answer);
        final String[] optionsArray = JsonHelper.jsonArrayToStringArray(options);
        return new MultiSelectQuiz(question, answerArray, optionsArray, solved);
    }

    private static Quiz createSelectItemQuiz(String question, String answer, String options,
                                             boolean solved) {
        final int[] answerArray = JsonHelper.jsonArrayToIntArray(answer);
        final String[] optionsArray = JsonHelper.jsonArrayToStringArray(options);
        return new SelectionItemQuiz(question, answerArray, optionsArray, solved);
    }

    private static Quiz createToggleTranslateQuiz(String question, String answer, String options,
                                                  boolean solved) {
        final int[] answerArray = JsonHelper.jsonArrayToIntArray(answer);
        final String[][] optionsArrays = extractOptionsArrays(options);
        return new ToggleTranslateQuiz(question, answerArray, optionsArrays, solved);
    }

    private static Quiz createTrueFalseQuiz(String question, String answer, boolean solved) {
    /*
     * parsing json with the potential values "true" and "false"
     * see res/raw/categories.json for reference
     */
        final boolean answerValue = "true".equals(answer);
        return new TrueFalseQuiz(question, answerValue, solved);
    }

    private static String[][] extractOptionsArrays(String options) {
        final String[] optionsLvlOne = JsonHelper.jsonArrayToStringArray(options);
        final String[][] optionsArray = new String[optionsLvlOne.length][];
        for (int i = 0; i < optionsLvlOne.length; i++) {
            optionsArray[i] = JsonHelper.jsonArrayToStringArray(optionsLvlOne[i]);
        }
        return optionsArray;
    }

    private static boolean getBooleanFromDatabase(String s) {
        return null != s && s.length() == 1 && Integer.valueOf(s) == 1;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CategoryTable.CREATE);
        db.execSQL(QuizTable.CREATE);
        preFillDatabase(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        /* no-op */
    }

    private void preFillDatabase(SQLiteDatabase db) {
        try {
            db.beginTransaction();
            try {
                fillCategoryAndQuizzes(db);
                db.setTransactionSuccessful();
            } finally {
                db.endTransaction();
            }
        } catch (IOException | JSONException e) {
            Log.e(TAG, "preFillDatabase", e);
        }
    }

    private void fillCategoryAndQuizzes(SQLiteDatabase db) throws IOException, JSONException {
        ContentValues values = new ContentValues();
        JSONArray array = new JSONArray(readCategoriesFromResource());
        JSONObject category;
        for (int i = 0; i < array.length(); i++) {
            category = array.getJSONObject(i);
            final String categoryId = category.getString(JsonAttributes.ID);
            fillCategory(db, values, category, categoryId);
            final JSONArray quizzes = category.getJSONArray(JsonAttributes.QUIZZES);
            fillQuizzes(db, values, quizzes, categoryId);
        }
    }

    private void fillCategory(SQLiteDatabase db, ContentValues values, JSONObject categories,
                              String categoryId) throws JSONException {
        values.clear();
        values.put(CategoryTable.COLUMN_ID, categoryId);
        values.put(CategoryTable.COLUMN_NAME, categories.getString(CategoryTable.COLUMN_NAME));
        values.put(CategoryTable.COLUMN_THEME, categories.getString(CategoryTable.COLUMN_THEME));
        values.put(CategoryTable.COLUMN_SCORES, categories.getString(CategoryTable.COLUMN_SCORES));
        values.put(CategoryTable.COLUMN_SOLVED, categories.getString(CategoryTable.COLUMN_SOLVED));
        db.insert(CategoryTable.NAME, null, values);
    }

    private void fillQuizzes(SQLiteDatabase db, ContentValues values, JSONArray quizzes, String quizId) throws JSONException {
        JSONObject quiz;
        for (int i = 0; i < quizzes.length(); i++) {
            quiz = quizzes.getJSONObject(i);
            values.clear();
            values.put(QuizTable.FK_CATEGORY, quizId);
            values.put(QuizTable.COLUMN_TYPE, quiz.getString(JsonAttributes.TYPE));
            values.put(QuizTable.COLUMN_QUESTION, quiz.getString(JsonAttributes.QUESTION));
            values.put(QuizTable.COLUMN_ANSWER, quiz.getString(JsonAttributes.ANSWER));
            putNonEmptyString(values, quiz, JsonAttributes.OPTIONS, QuizTable.COLUMN_OPTIONS);
            putNonEmptyString(values, quiz, JsonAttributes.MIN, QuizTable.COLUMN_MIN);
            putNonEmptyString(values, quiz, JsonAttributes.MAX, QuizTable.COLUMN_MAX);
            putNonEmptyString(values, quiz, JsonAttributes.STEP, QuizTable.COLUMN_STEP);
            putNonEmptyString(values, quiz, JsonAttributes.START, QuizTable.COLUMN_START);
            putNonEmptyString(values, quiz, JsonAttributes.END, QuizTable.COLUMN_END);
            db.insert(QuizTable.NAME, null, values);
        }

    }

    /**
     * Puts a non-empty string to ContentValues provided.
     *
     * @param values
     * @param quiz
     * @param jsonKey
     * @param contentKey
     */
    private void putNonEmptyString(ContentValues values, JSONObject quiz, String jsonKey, String contentKey) {
        final String stringToPut = quiz.optString(jsonKey, null);
        if (!TextUtils.isEmpty(stringToPut)) {
            values.put(contentKey, stringToPut);
        }
    }

    /**
     * obtain the origin string from raw/categories.json
     *
     * @return json string
     * @throws IOException
     */
    private String readCategoriesFromResource() throws IOException {
        StringBuilder categoriesJson = new StringBuilder();
        InputStream rawCategories = mResources.openRawResource(R.raw.categories);
        BufferedReader reader = new BufferedReader(new InputStreamReader(rawCategories));
        String line;
        while ((line = reader.readLine()) != null) {
            categoriesJson.append(line);
        }
        return categoriesJson.toString();
    }
}
